﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using System.Linq;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using System.Windows.Forms;
using System.Collections;
using System.IO;

namespace SQLServerHealthCheck.SQLServer
{
    public static  class  DatabasesCheck
    {
        public static List<Exception> errors;
        public static ArrayList arrDatabaseCheck = new ArrayList();
        public static List<Exception> GenerateDatabasesOverview(List<String> SQLServerInstances)
        {
            try
            {
                errors = new List<Exception>();

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].ToString());

                dc.ClearAll();

                List<Server> servers = new List<Server>();

             
                ////delete the Installation Check Log file if it exists
                //string InstallationCheckLogfile = Application.StartupPath + "\\SQLServerHealthCheck_InstallationCheckLog.txt";

                //if (File.Exists(InstallationCheckLogfile))
                //{
                //    File.Delete(InstallationCheckLogfile);
                //}

                ////delete the Database Check Log file if it exists
                //string DatabaseCheckLogfile = Application.StartupPath + "\\SQLServerHealthCheck_DatabaseCheckLog.txt";

                //if (File.Exists(DatabaseCheckLogfile))
                //{
                //    File.Delete(DatabaseCheckLogfile);
                //}

                InstallationCheck.arrInstallationCheck.Clear();
                arrDatabaseCheck.Clear();
                InstallationCheck.FillServersAndSQLServerServices(SQLServerInstances, dc, servers);
                InstallationCheck.FillServices(dc, servers);
                FillDatabasesAndFiles(dc, servers);

               
            }
            catch (System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to delete file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return errors;
        }

        public static void FillDatabasesAndFiles(HealthCheckDataContext dc, List<Server> servers)
        {
            foreach (Server server in servers)
            {
                foreach (Service service in server.Services)
                {
                    if (service.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer)
                    {
                        FillDatabasesAndFiles(dc, service);
                    }
                }

            }
        }

        private static void FillDatabasesAndFiles(HealthCheckDataContext dc, Service service)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (InstallationCheck.IsSQLServerAccessible(@"Server=" + service.InstanceName.ToString() + ";database=master;Integrated Security=True"))
                {
                    using (SqlConnection cn = new SqlConnection(@"Server=" + service.InstanceName.ToString() + ";Database=master;Integrated Security=True"))
                    {
                        cn.Open();
                        String sql = @"

	
	
	Create table #Database_BackupDetails(
	  
		DatabaseName varchar(200),
		LatestFullBackup [datetime] NULL,
		LatestDifferentialBackup [datetime] NULL,
		LatestLogBackup [datetime] NULL
	)
	
	
	Create table #Database_FileDetails(
	     [DatabaseID] int,
		DatabaseName varchar(200),
		Collation varchar(500) NULL,
		Create_date [datetime] NULL,
		[Owner] varchar(200) NULL,
		RecoveryModel varchar(20) NULL,
		State varchar(100) NULL,
		[CompatibilityLevel] [varchar](50) NULL,
		
	[File] varchar(150) NULL, 
	FileGroup varchar(150) NULL, 
	Drive varchar(50) NULL,
	SizeInMb decimal NULL
	)


declare @query varchar(8000)
Set @query=''
DECLARE 
	@databaseID int,
	@database varchar(150),
	@collation varchar(500),
	@owner varchar(200),
	@recoveryModel varchar(20),
	@state varchar(100),
	@create_date [datetime] ,
	@LatestFullBackup [datetime] ,
	@LatestDifferentialBackup [datetime] ,
	@LatestLogBackup [datetime] ,
	@CompatibilityLevel [varchar](50)

	
		
DECLARE databases_cursor CURSOR FOR
	SELECT d.name , d.database_id , d.collation_name,d.Create_date, p.name as Owner, d.recovery_model_desc, d.state_desc, d.compatibility_level
	FROM sys.databases d LEFT OUTER JOIN sys.server_principals p  ON d.owner_sid=p.sid where d.state!=1



OPEN databases_cursor

FETCH NEXT FROM databases_cursor INTO @database, @databaseid, @collation,@create_date ,@owner, @recoveryModel, @state, @CompatibilityLevel

WHILE @@FETCH_STATUS = 0
 BEGIN
 
Set @query='insert into #Database_BackupDetails( DatabaseName,	LatestFullBackup,LatestDifferentialBackup,LatestLogBackup) (
	
	 SELECT [Database] , [Full] AS LatestFullBackup, [Differential] AS LatestDifferentialBackup, [Log] as LatestLogBackupDate
				FROM
				(
	SELECT 
						DATABASE_NAME [Database],
						BackupType=
							CASE 
								WHEN [TYPE]=''D'' THEN ''Full'' 
								WHEN [TYPE]=''I'' THEN ''Differential'' 
								WHEN [TYPE]=''L'' THEN ''Log''
								WHEN [TYPE]=''F'' THEN ''File / Filegroup''
								WHEN [TYPE]=''G'' THEN ''Differential file''
								WHEN [TYPE]=''P'' THEN ''Partial''
								WHEN [TYPE]=''Q'' THEN ''Differential partial''
							END,
						cast(MAX(BACKUP_DATE) as varchar(100)) BACKUP_DATE
					FROM 
						(
							SELECT 
								ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,
								A.BACKUP_FINISH_DATE AS BACKUP_DATE, 
								B.PHYSICAL_DEVICE_NAME,
								A.MEDIA_SET_ID,
								A.DATABASE_NAME,
								[TYPE]
							FROM 
								MSDB.DBO.BACKUPSET A JOIN 
								MSDB.DBO.BACKUPMEDIAFAMILY B ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
								where A.database_name=''' + @database +
						''') AS SUB
					GROUP BY [TYPE],DATABASE_NAME
					
				) AS S 
				PIVOT
				(
					min(BACKUP_DATE)
					FOR BackupType IN([Full] ,[Differential],[Log])
				) AS P

)


insert into #Database_FileDetails([DatabaseID],DatabaseName,Collation ,	Create_date ,[Owner],RecoveryModel,State,[CompatibilityLevel] ,Drive,Filegroup,[File],SizeinMB)(
SELECT	' + cast(@databaseid as varchar(100)) + ','''+@database + ''','''+@collation + ''','''+ cast(@create_date as varchar(100)) + ''','''+@owner + ''','''+@recoveryModel+ ''','''+@state + ''','''+@CompatibilityLevel +''''+ ',Left(df.physical_name,3) AS Drive,ISNULL(f.name,''LOG'') AS FileGroup,
 RIGHT(df.physical_name, CHARINDEX(''\'', REVERSE(df.physical_name))-1) AS [File], 
							df.size*8/1024  AS SizeInMb
 
 FROM         
							[' + @database + '].sys.database_files df LEFT OUTER JOIN
							[' + @database + '].sys.filegroups f ON df.data_space_id = f.data_space_id  )'

exec( @query)

FETCH NEXT FROM databases_cursor INTO @database, @databaseid, @collation,@create_date ,@owner, @recoveryModel, @state, @CompatibilityLevel

 END
 
CLOSE databases_cursor
DEALLOCATE databases_cursor

select DF.DatabaseID as DatabaseID,DF.DatabaseName as DatabaseName,DF.Collation as Collation,DF.[Owner] as [Owner],
DF.RecoveryModel as RecoveryModel,DF.[State] as [State],DF.CompatibilityLevel as CompatibilityLevel, DatabaseProperty (DF.DatabaseName, 'version') as DatabaseVersion,
DF.Create_date as CreateDate,[DF].[File] as [File],DF.FileGroup as FileGroup,
DF.Drive as Drive,DF.SizeInMb as SizeInMb,DB.LatestFullBackup as LatestFullBackup,DB.LatestDifferentialBackup as LatestDifferentialBackup,
DB.LatestLogBackup as LatestLogBackup from  #Database_FileDetails as DF  left outer join #Database_BackupDetails   as DB
on DF.DatabaseName=DB.DatabaseName 


drop table #Database_FileDetails
drop table #Database_BackupDetails





";

                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();

                        while (rd.Read())
                        {
                            Database db = null;

                            if (service.Databases.Count(d => d.DatabaseName == rd[1].ToString()) == 0)
                            {
                                db = new Database();

                                db.DatabaseName = rd[1].ToString();
                                db.Collation = rd[2].ToString();
                                db.Owner = rd[3].ToString();
                                db.RecoveryModel = rd[4].ToString();
                                db.State = rd[5].ToString();
                                db.CompatibilityLevel = rd[6].ToString();
                                db.DatabaseVersion = rd[7].ToString();
                                db.CreateDate = Convert.ToDateTime(rd[8].ToString());
                                db.LatestFullBackup = rd.IsDBNull(13) ? (DateTime?)null : rd.GetDateTime(13);
                                db.LatestDifferentialBackup = rd.IsDBNull(14) ? (DateTime?)null : rd.GetDateTime(14);
                                db.LatestLogBackup = rd.IsDBNull(15) ? (DateTime?)null : rd.GetDateTime(15);

                                service.Databases.Add(db);

                            }
                            else
                            {
                                db = service.Databases.First(d => d.DatabaseName == rd[1].ToString());
                            }

                            DatabaseFile dbfile = new DatabaseFile();

                            int offset = 9;

                            dbfile.File = rd[offset].ToString();
                            dbfile.FileGroup = rd[offset + 1].ToString();
                            dbfile.Drive = rd[offset + 2].ToString();
                            dbfile.SizeInMb = rd.GetDecimal(offset + 3);

                            db.DatabaseFiles.Add(dbfile);


                        }

                        rd.Close();


                    }

                    dc.SubmitChanges();

                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Completed for " + service.InstanceName.ToString());

                }

                else
                {
                    //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                    arrDatabaseCheck.Add(service.InstanceName.ToString());
                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " There were problems in importig DatabaseCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
                }
            }

            catch (NullReferenceException ex1)
            {

                
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " Some error occured in Database Check.Please check exception file for details");
            }

            catch (Exception ex)
            {

                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " Some error occured in Database Check.Please check exception file for details");
            }



        }

    }
}
